package com.eascs.crm.nsEditDetail.service;

import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.util.StringUtil;
import com.eascs.crm.common.vo.ModelResult;
import com.eascs.crm.nsEditDetail.dao.NsEditDetailDao;
import com.eascs.crm.nsEditDetail.entity.CustomerShopInfoEntity;
import com.eascs.crm.nsEditDetail.entity.CustomerShopInfoEntityNew;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class CustomerShopInfoService {
	private Logger logger = LoggerFactory.getLogger(this.getClass());

	@Autowired
	NsEditDetailDao nsDao;
	
	public ModelResult<CustomerShopInfoEntity> getShopMarketMoney(CustomerShopInfoEntity customerShopInfoEntity) {
		ModelResult<CustomerShopInfoEntity> modelResult = new ModelResult<CustomerShopInfoEntity>();
		if(StringUtil.isBlank(customerShopInfoEntity.getLoanapplyNo())){
			return modelResult.withError("error", "参数异常：customerId不能为空");
		}
		try {
			String sql = "SELECT sum(IFNULL(t.total_amount, 0)) AS totalAmount, SUM(IFNULL(t.shoptock, 0)) AS shoptock FROM customer_shop_info t WHERE t.islock = 'N' AND t.loanapply_no = ?";
			CustomerShopInfoEntity entity = nsDao.findEntity(sql, CustomerShopInfoEntity.class, customerShopInfoEntity.getLoanapplyNo());
			modelResult.setModel(entity);
		} catch (Exception e) {
			logger.info("getShopMarketMoney err", e);
			modelResult.withError("500", "服务器异常");
		}
		return modelResult;
	}

	public boolean insert(CustomerShopInfoEntity info) {
		return nsDao.insert(info) > 0;
	}
	public boolean insertnew(CustomerShopInfoEntityNew info) {
		return nsDao.insert(info) > 0;
	}
	
	public boolean delete(String id) {
		return nsDao.execute("UPDATE customer_shop_info t SET t.islock='Y' WHERE t.customer_shop_id=?", id) > 0;
	}

	public boolean update(CustomerShopInfoEntity info) {
		return nsDao.updateById(info) > 0;
	}
	public boolean updatenew(CustomerShopInfoEntityNew info) {
		return nsDao.updateById(info) > 0;
	}
	public CustomerShopInfoEntity queryById(String id) {
		return nsDao.findEntity("SELECT * FROM customer_shop_info t WHERE t.customer_shop_id=? ", CustomerShopInfoEntity.class, id);
	}
	public CustomerShopInfoEntityNew queryByIdnew(String id) {
		return nsDao.findEntity("SELECT * FROM customer_shop_info t WHERE t.customer_shop_id=? ", CustomerShopInfoEntityNew.class, id);
	}
	/**
	 * @Description:
	 *               <p>
	 *               根据loanapplyNo查询主体门店的信息
	 *               </p>
	 * @author LMX
	 * @param loanapplyNo
	 * @return
	 */
	public CustomerShopInfoEntity queryCustomerMainShopByLoanapplyNo(String loanapplyNo) {
		return nsDao.findEntity("SELECT * FROM customer_shop_info t WHERE t.loanapply_no=? AND t.islock='N' AND t.shoptype='MAN'", CustomerShopInfoEntity.class, loanapplyNo);
	}
	public List<CustomerShopInfoEntity> list(String loanapplyNo) {
		String sql = "SELECT * FROM customer_shop_info t WHERE t.loanapply_no=?  AND t.islock='N'";
		return nsDao.findEntitys(sql, CustomerShopInfoEntity.class, loanapplyNo);
	}

	public PageResult<Object[]> page(String loanapplyNo, int pageNo) {
		String sql = "SELECT t.customer_shop_id, t.shopname, t.shoptype, DATE_FORMAT(t.stupdate,'%Y-%m-%d'),DATE_FORMAT(t.eacooperationdate,'%Y-%m-%d') , t.vocation " +
				" FROM customer_shop_info t WHERE t.loanapply_no=? AND t.islock='N' ";

		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, 10, pageNo);
		sqlPageQuery.addParam(loanapplyNo);
		sqlPageQuery.setQuerySql(sql);


		return nsDao.findDataWithPage(sqlPageQuery);
	}
	
	/**
	 * @Description:
	 *               <p>
	 *               分页查询新门店信息--门店地址列表【脱敏】
	 *               </p>
	 * @author LMX
	 */
	public PageResult<Object[]> pagenewSensitive(String loanapplyNo,String type,String approvalStatus) {
		StringBuffer sql=new StringBuffer();
		
		if(StringUtil.isNotBlank(approvalStatus)){
			if (!"UNDO".equals(approvalStatus)) {
				sql.append("SELECT t.customer_shop_id, property_type, DATE_FORMAT(t.stupdate,'%Y-%m-%d'),");
				sql.append("concat(DATE_FORMAT(t.shoprent_start,'%Y-%m-%d'),'到',DATE_FORMAT(t.shoprent_end,'%Y-%m-%d')) as shoprentdate, ");
				sql.append(" CONCAT( LEFT ( CONCAT( ifNULL(");
				sql.append(" (SELECT m.item_name from  pub_dditem_crm m where m.dd_no=(");
				sql.append(" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='PROVINCE'");
				sql.append(" ) and m.item_code=t.shopprovince");
				sql.append(" ),'') ,ifNULL(");
				sql.append(" (SELECT m.item_name from  pub_dditem_crm m where m.dd_no=(");
				sql.append(" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='city'");
				sql.append(" ) and m.item_code=t.shopcity");
				sql.append(" ),'') ,ifNULL(");
				sql.append(" (SELECT m.item_name from  pub_dditem_crm m where m.dd_no=(");
				sql.append(" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='country'");
				sql.append(" ) and m.item_code=t.shopcounty");
				sql.append(" ),''), ifNULL(t.shopaddress,'')   ), 6), '******') as addressname ");
				sql.append(" FROM customer_shop_info t WHERE t.loanapply_no=? AND t.islock='N' ");
			}else {
				sql.append("SELECT t.customer_shop_id, property_type, DATE_FORMAT(t.stupdate,'%Y-%m-%d'),");
				sql.append("concat(DATE_FORMAT(t.shoprent_start,'%Y-%m-%d'),'到',DATE_FORMAT(t.shoprent_end,'%Y-%m-%d')) as shoprentdate, ");
				sql.append(" CONCAT(   ifNULL(");
				sql.append(" (SELECT m.item_name from  pub_dditem_crm m where m.dd_no=(");
				sql.append(" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='PROVINCE'");
				sql.append(" ) and m.item_code=t.shopprovince");
				sql.append(" ),'') ,ifNULL(");
				sql.append(" (SELECT m.item_name from  pub_dditem_crm m where m.dd_no=(");
				sql.append(" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='city'");
				sql.append(" ) and m.item_code=t.shopcity");
				sql.append(" ),'') ,ifNULL(");
				sql.append(" (SELECT m.item_name from  pub_dditem_crm m where m.dd_no=(");
				sql.append(" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='country'");
				sql.append(" ) and m.item_code=t.shopcounty");
				sql.append(" ),''), ifNULL(t.shopaddress,'')   ) as addressname ");
				sql.append(" FROM customer_shop_info t WHERE t.loanapply_no=? AND t.islock='N' ");
			}
		}else {
			if (!"detail".equals(type)) {
				sql.append("SELECT t.customer_shop_id, property_type, DATE_FORMAT(t.stupdate,'%Y-%m-%d'),");
				sql.append("concat(DATE_FORMAT(t.shoprent_start,'%Y-%m-%d'),'到',DATE_FORMAT(t.shoprent_end,'%Y-%m-%d')) as shoprentdate, ");
				sql.append(" CONCAT( LEFT ( CONCAT( ifNULL(");
				sql.append(" (SELECT m.item_name from  pub_dditem_crm m where m.dd_no=(");
				sql.append(" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='PROVINCE'");
				sql.append(" ) and m.item_code=t.shopprovince");
				sql.append(" ),'') ,ifNULL(");
				sql.append(" (SELECT m.item_name from  pub_dditem_crm m where m.dd_no=(");
				sql.append(" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='city'");
				sql.append(" ) and m.item_code=t.shopcity");
				sql.append(" ),'') ,ifNULL(");
				sql.append(" (SELECT m.item_name from  pub_dditem_crm m where m.dd_no=(");
				sql.append(" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='country'");
				sql.append(" ) and m.item_code=t.shopcounty");
				sql.append(" ),''), ifNULL(t.shopaddress,'')   ), 6), '******') as addressname ");
				sql.append(" FROM customer_shop_info t WHERE t.loanapply_no=? AND t.islock='N' ");
			}else{
				sql.append("SELECT t.customer_shop_id, property_type, DATE_FORMAT(t.stupdate,'%Y-%m-%d'),");
				sql.append("concat(DATE_FORMAT(t.shoprent_start,'%Y-%m-%d'),'到',DATE_FORMAT(t.shoprent_end,'%Y-%m-%d')) as shoprentdate, ");
				sql.append(" CONCAT(   ifNULL(");
				sql.append(" (SELECT m.item_name from  pub_dditem_crm m where m.dd_no=(");
				sql.append(" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='PROVINCE'");
				sql.append(" ) and m.item_code=t.shopprovince");
				sql.append(" ),'') ,ifNULL(");
				sql.append(" (SELECT m.item_name from  pub_dditem_crm m where m.dd_no=(");
				sql.append(" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='city'");
				sql.append(" ) and m.item_code=t.shopcity");
				sql.append(" ),'') ,ifNULL(");
				sql.append(" (SELECT m.item_name from  pub_dditem_crm m where m.dd_no=(");
				sql.append(" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='country'");
				sql.append(" ) and m.item_code=t.shopcounty");
				sql.append(" ),''), ifNULL(t.shopaddress,'')   ) as addressname ");
				sql.append(" FROM customer_shop_info t WHERE t.loanapply_no=? AND t.islock='N' ");
			}
		}
		
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		sqlPageQuery.addParam(loanapplyNo);
		sqlPageQuery.setQuerySql(sql.toString());
		
		return nsDao.findDataWithPage(sqlPageQuery);
	}
	
	public PageResult<Object[]> pagenew(String loanapplyNo) {
//		String sql = "SELECT t.customer_shop_id, property_type, DATE_FORMAT(t.stupdate,'%Y-%m-%d'),DATE_FORMAT(t.eacooperationdate,'%Y-%m-%d') , t.vocation " +
//				" FROM customer_shop_info t WHERE t.loanapply_no=? AND t.islock='N' ";
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT t.customer_shop_id, property_type, DATE_FORMAT(t.stupdate,'%Y-%m-%d'),");
		sql.append("concat(DATE_FORMAT(t.shoprent_start,'%Y-%m-%d'),'到',DATE_FORMAT(t.shoprent_end,'%Y-%m-%d')) as shoprentdate, ");
		sql.append(" concat(ifNULL(");
		sql.append(" (SELECT m.item_name from  pub_dditem_crm m where m.dd_no=(");
		sql.append(" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='PROVINCE'");
		sql.append(" ) and m.item_code=t.shopprovince");
		sql.append(" ),'') ,ifNULL(");
		sql.append(" (SELECT m.item_name from  pub_dditem_crm m where m.dd_no=(");
		sql.append(" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='city'");
		sql.append(" ) and m.item_code=t.shopcity");
		sql.append(" ),'') ,ifNULL(");
		sql.append(" (SELECT m.item_name from  pub_dditem_crm m where m.dd_no=(");
		sql.append(" SELECT y.dd_no FROM  pub_datadictionary_crm Y WHERE  Y.dd_code='country'");
		sql.append(" ) and m.item_code=t.shopcounty");
		sql.append(" ),''), ifNULL(t.shopaddress,'')   ) as addressname ");
		sql.append(" FROM customer_shop_info t WHERE t.loanapply_no=? AND t.islock='N' ");
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		sqlPageQuery.addParam(loanapplyNo);
		sqlPageQuery.setQuerySql(sql.toString());


		return nsDao.findDataWithPage(sqlPageQuery);
	}
	public PageResult<Object[]> page(String loanapplyNo) {
		return this.page(loanapplyNo, 0);
	}
}
